package com.qf.shiroboot.service.impl;

import com.qf.shiroboot.entity.Role;
import com.qf.shiroboot.service.RoleService;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashSet;
import java.util.Set;


@Service
public class RoleServiceImpl implements RoleService {


    @Override
    public Set<Role> findAllRolesByUserId(Integer id) {
        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //2. 获取连接对象
            Connection connection = DriverManager.getConnection("jdbc:mysql:///shiro", "root", "root");

            //3. 声明SQL
            String sql =    "select " +
                            "  r.id id, " +
                            "  r.role_name roleName " +
                            "from " +
                            "  role r inner join users_roles ur on r.id = ur.role_id " +
                            "  inner join `user` u on u.id = ur.user_id " +
                            "where " +
                            "  u.id = ?";

            //4. 预执行SQL，设置参数
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setObject(1,id);

            //5. 执行SQL获取返回结果
            ResultSet rs = ps.executeQuery();

            //6. 遍历返回结果并封装对象
            Set<Role> roles = new HashSet<>();
            while(rs.next()){
                Role role = new Role();
                role.setId(rs.getInt("id"));
                role.setRoleName(rs.getString("roleName"));
                roles.add(role);
            }

            //7. 释放资源
            rs.close();
            ps.close();
            connection.close();
            //8. 返回数据
            return roles;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
